/*
Copyright (c) 2012, Human Race
All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

    Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
    Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

classes to simulate Web SQL Database API
(ref: http://www.w3.org/TR/webdatabase/#sql)

*/

// Check if console object exists
if (!window.console) window.console = {};
if (!window.console.log) window.console.log = function () { };

window["undefined"] = undefined;

//define namespace, to avoid conflict
HPI = {};

HPI.apply = function(obj, prop){

	for(var name in prop){
		obj[name] = prop[name];
	}
	
	return obj;
}

HPI.applyIf = function(obj, prop){

	for(var name in prop){
		
		if( obj[name] != undefined ) continue;
		
		obj[name] = prop[name];
	
	}
	
	return obj;
	
}

HPI.each = function(set, callback, scope){

	var r = true;
	scope = scope || window;
	if( set instanceof Array ){
		for(var i = 0, len = set.length; i < len; i++){
			r = callback.apply(scope, [i, set[i], set]);
			if( r === false ){
				break;
			}
		}
	}
	else{
		for(var i in set){
			r = callback.apply(scope, [i, set[i], set]);
			if( r === false ){
				break;
			}			
		}
	}
	
}

HPI.isString = function(v){
	if( v == null ) return false;
	var toString = Object.prototype.toString;
	return toString.call(v) === '[object String]';
}

HPI.isArray = function(v){
	
	if ( v == null ) return false;

	var toString = Object.prototype.toString;
	return toString.call(v) === '[object Array]';
	
}

HPI.isScalar = function(v){
	if( v == null ) return true;
	var toString = Object.prototype.toString;
	return !( HPI.isArray(v) || toString.call(v) === '[object Object]');
}

HPI.isNonScalar = function(v){
	return HPI.isScalar(v) == false;
}

HPI.hasCompatibleTypes = function(v1, v2){
	
	var toString = Object.prototype.toString;
	return (toString.call(v1) == toString.call(v2) && v1.constructor == v2.constructor);
	
}

HPI.isEqual = function(v1, v2, isStrict, isInRelExp){
	
	isStrict = (isStrict == undefined ? true : isStrict);	
	isInRelExp = (isInRelExp == undefined ? false : isInRelExp);
	
	if( v1 == null || v2 == null ){
		//equality operations involving NULL should always evaluate to false
		if( isStrict ) {
			return false;
		}
		return v1 == v2;
	}
	
	//relation "equality" comparison treats 1 and "1" as equal. so convert everything to string.
	if( isInRelExp ){
		v1 = v1.toString();
		v2 = v2.toString();
		return v1 == v2;
	}
	
	//check if have compatible types
	if( !HPI.hasCompatibleTypes(v1, v2) ){
		return false;
	}
	
	var v1IsScalar = HPI.isScalar(v1)
	    ,v2IsScalar = HPI.isScalar(v2);
		
	switch(true){
	
		//both scalar
		case v1IsScalar && v2IsScalar : {
		
			if( HPI.isString(v1) && HPI.isString(v2) ){
				return v1.toUpperCase() == v2.toUpperCase();
			}
			
			return v1 === v2;
			
		}
		
		//both are non-scalar
		case !v1IsScalar && !v2IsScalar : {
		
			if( HPI.count(v1) != HPI.count(v2) ){
				return false;
			}
			
			var r = true;
			
			HPI.each(v1, function(idx, item, items){
			
				if( v2[idx] === undefined ){
					r = false;
					return false; //break the each function
				}
				
				if( false == HPI.isEqual(item, v2[idx], isStrict, isInRelExp) ){
					r = false;
					return false; //break the each function
				}

			}, this);
			
			return r;
			
		}
		
		default : {
			return false;
		}
		
	}
	
}

HPI.count = function(val){

	if( val.length != undefined ){
		return val.length;
	}
	
	var cnt = 0;
		
	if( HPI.isScalar(val) ){
		throw new Error("Cannot determine cardinality for scalar values.");
	}
	
	HPI.each(val, function(index, item, items){
		cnt++;
	}, this);
	
	return cnt;

}

HPI.isEmpty = function(val){

	var isEmpty = true;
	
	if( HPI.isNonScalar(val) ){
		HPI.each(val, function(idx, item, items){
			isEmpty = false;
			return false;
		});		
	}
	else{
		if( HPI.isString(val) && val !== '' ){
			isEmpty = false;
		}
		else if ( !isNaN(val) && val !== 0 ){
			isEmpty = false;
		}		
	}
	
	return isEmpty;
}


/*
returns true if row is found in $rows.
let rows = array( array('field1'=>1, 'field2'=>2), array('field1'=>2, 'field2'=>3) )
let row   = array('field1'=>1,'field2'=>2)
then hpi_in_array(row, rows) returns true;
*/
HPI.inArray = function(row, rows, isStrict, isInRelExp){

	row = HPI.isScalar(row) ? [row] : row;
	
	var result = false
	    ,givenRowCount    = HPI.count(row)
	    ,givenRowIsEmpty = (givenRowCount == 0);

	HPI.each(rows, function(idx, _row, allRows){
		
		result = false;
		
		_row = HPI.isScalar(_row) ? [_row] : _row;
		
		var _rowCount = HPI.count(_row);

		if( givenRowCount == _rowCount ){
			
			if( givenRowCount == 0 ){
				result = true;
				return false; // break the loop
			}
			else{
				var _r = true;
				HPI.each(_row, function(field, val, allFields){					
					_r = _r && HPI.isEqual(val, row[field], isStrict, isInRelExp);
					if( ! _r  ){
						return false; //break the loop
					}
				}, this);
				
				if(  _r ) {
					result = true;
					return false;  //break the loop
				}
			}
			
		}
		
	}, this);
	
	return result;
	
}

/*

$merge = array(
	array('field1'=>1)
   ,array('field1'=>2)
);


$sorted = array(
	array('field1'=>1, 'field2'=>'a')
   ,array('field1'=>2, 'field2'=>'c')
   ,array('field1'=>3, 'field2'=>'d')
);

result:
array(
	array('field1'=>1, 'field2'=>'a')
   ,array('field1'=>2, 'field2'=>'c')
);

In short:
if $merge and $sorted are tables, then _merge_sorted($merge, $sorted) is similar to this SQL statement:

SELECT merge.field1, sorted.field2 FROM merge INNER JOIN sorted ON
merge.field1 = sorted.field1;

that is, both array are JOINed over using their common attributes (i.e., field1)
*/

HPI.mergeSorted = function(merge, sorted){
	
	var r1 = merge
		,r2 = sorted
		,b  = [];
	
	for(var i = 0, len = r1.length; i  < len; i++){
		var t1 = r1[i];
		for(var j = 0, len1 = r2.length; j < len1; j++){
			
			var t2 = r2[j], toInclude = true;
			
			for (var fieldName in t2 ) {
				var val = t2[fieldName];
				if( undefined != t1[fieldName] && t1[fieldName] != val){
					//continue 2;
					toInclude = false;
					break;
				}
			}
			
			if( toInclude ){
				b = b.concat([HPI.apply(HPI.apply({}, t1), t2)]);
			}
		}
	}
	
	return b;

}

/*
implements the actual sorting, using quicksort, for table/relational expressions.
$pDataSet is an array of associated array. $pKey is the key/field you want to sort, and $pSortType is
the direction (ASC or DESC).
*/
HPI.sort = function(pDataSet, pLow, pHigh, pKey, pSortType, sortFn, options){
	
    options = options || {};
	pSortType = pSortType || "asc";

    var isNoCase = typeof options.collation == 'string' && (options.collation.toUpperCase() == 'NOCASE');

	sortFn = sortFn || function(v1, v2, dir){
	
        if( isNoCase ){
			v1 = v1.toString().toUpperCase();
			v2 = v2.toString().toUpperCase();
		}

		dir = dir.toUpperCase();
		
		return dir == 'ASC' ? v1 < v2 : v1 > v2;
	}
	
	var Lo = pLow
	    ,Hi = pHigh
	    ,pSortType = pSortType.toUpperCase()
		,Mid = pDataSet[Math.floor((Lo + Hi)/2)][pKey];
	
	
	if(pSortType == "DESC"){
	
		while(Lo < Hi){
		
			var _l  = pDataSet[Lo][pKey]
			    ,_h = pDataSet[Hi][pKey];
				
			//while(_l > Mid) {
			while(sortFn(_l, Mid, 'desc')) {
				Lo++;
				_l = pDataSet[Lo][pKey];
			}
			
			//while(_h < Mid) {
			while(sortFn(_h, Mid, 'asc')){
				Hi--;
				_h = pDataSet[Hi][pKey];
			}
			 
			if(Lo <= Hi){
				var Temp = pDataSet[Lo];
				pDataSet[Lo] = pDataSet[Hi];
				pDataSet[Hi] = Temp;
				Lo++;
				Hi--; 
			}
		
		}
		
	}
	else{ 
	
		while(Lo < Hi){
		
			var _l  = pDataSet[Lo][pKey]
			    ,_h = pDataSet[Hi][pKey];
				
			//while(_l < Mid) {
			while(sortFn(_l, Mid, 'asc')) {
				Lo++;
				_l = pDataSet[Lo][pKey];
			}
			
			//while(_h > Mid){
			while(sortFn(_h,Mid, 'desc')){
				Hi--;
				_h = pDataSet[Hi][pKey];
			}
			
			if(Lo <= Hi){
				var Temp = pDataSet[Lo]
					,Temp_ = pDataSet[Hi];
				
				pDataSet[Lo] = Temp_;
				pDataSet[Hi] = Temp;
				Lo++;
				Hi--; 
			}
		}
	
	}
	
	if (Hi > pLow){
		pDataSet = HPI.sort(pDataSet, pLow, Hi, pKey, pSortType, sortFn, options);
	}
	
	if (Lo < pHigh){
		pDataSet = HPI.sort(pDataSet, Lo, pHigh, pKey, pSortType, sortFn, options);
	}

	return pDataSet;

}

/*

given an array:
$data = array(
	array('field1'=>1, 'field2'=>'x')
   ,array('field1'=>2, 'field2'=>'c')
   ,array('field1'=>1, 'field2'=>'d')
   ,array('field1'=>1, 'field2'=>'g')
);

_keys($data, array('field1'));

results to:

array(
	array('field1'=>1)
   ,array('field1'=>2)
);


In short:
if $data is the records of an SQL table, then _keys($data, array('field1')) is similar to:

SELECT DISTINCT field FROM data;

*/

HPI.keys = function(data, keys){

	var ret = [];
	
	for(var i = 0, len = data.length; i < len; i++){
		
		var row  = data[i]
		    ,_row = {};
		
		for(var j = 0, jLen = keys.length; j < jLen; j++){
			var key = keys[j];
			_row[key] = row[key];
		}
		
		if( false == HPI.inArray(_row, ret) ){
			ret = ret.concat([_row]);
		}
		
	}

	return ret;
	
}

//-----------------

HPI.SQLResultSetRowList  = Class.extend({
	

	length : 0
   
   ,_data : []
   
   ,init : function(data){
		this._data = data;
		this.length = data.length;
	}
	
   ,item : function(index){
	
		return this._data[index] || null;
	}

});

//introduce the standard class

if( 'undefined' == typeof SQLResultSetRowList ){
	SQLResultSetRowList = HPI.SQLResultSetRowList;
}

HPI.SQLResultSet = Class.extend({

    insertId : 0
	
   ,rowsAffected : 0
   
   ,rows :  null
   
   ,init : function(cfg){
		this.rowsAffected = cfg.rowsAffected || 0;
		this.insertId        = cfg.insertId || undefined;
		this.rows            = new HPI.SQLResultSetRowList(cfg.data || []);
	}
	
});

//introduce the standard class
if( 'undefined' == typeof SQLResultSet ){
	SQLResultSet = HPI.SQLResultSet;
}

HPI.SQLError = Class.extend({
	
	message : ''
   
   ,code : -1
   
   ,init : function(msg, code){
		
		this.message = msg  || '';
		this.code       = code === undefined ? -1 : code;
		
   }

});


HPI.SQLError.UNKNOWN_ERR = 0;
HPI.SQLError.DATABASE_ERR = 1;
HPI.SQLError.VERSION_ERR = 2;
HPI.SQLError.TOO_LARGE_ERR = 3;
HPI.SQLError.QUOTA_ERR = 4;
HPI.SQLError.SYNTAX_ERR = 5;
HPI.SQLError.CONSTRAINT_ERR = 6;
HPI.SQLError.TIMEOUT_ERR = 7;

//introduce the standard class
if( 'undefined' == typeof SQLError ){
	SQLError = HPI.SQLError;
}

HPI.SQLTransaction = Class.extend({

	_db : null
   
   ,init : function(db){
		this._db = db;
	}
   
   ,executeSql : function(sqlStatement, args, successCallback, errorCallback){
		
		var me = this;
		var _exec = function(){
			me.sql = sqlStatement;
			var parseTree = null
				,exc = null
				,result = {};
		
			var escape = function(val){
				return typeof val == 'string' ? ("'" + val.replace("'", "''") + "'") : val;
			}
			
			for(var i = 0, len = args.length; i < len; i++){
				sqlStatement = sqlStatement.replace('?', args[i]);
			}

			try{
				parseTree = SQLParser.parse(sqlStatement);
			}
			catch(e){
				if( typeof errorCallback == 'function' ){
					errorCallback.apply(window, [me,  new HPI.SQLError(e.toString(), HPI.SQLError.SYNTAX_ERR)]);
				}
				return;
			}
			
			var drver = me._db.getStorageDriver();
			drver._proc(parseTree, result, successCallback, errorCallback, me);			
			
		}
		
		_exec();

	}
	
	
});

//introduce the standard class
if( 'undefined' == typeof SQLTransaction ){
	SQLTransaction = HPI.SQLTransaction;
}


HPI.SQLDatabase = Class.extend({

    _storageDriver : null

   ,init : function(cfg){
		
	   var strDriver = cfg.storageDriver;	   
       this.storageDriver = function () {
           return strDriver;
       }

       delete cfg.storageDriver;
       
       HPI.apply(this, cfg);
       
       this._storageDriver = new HPI[strDriver](cfg);
		
	}

   ,transaction : function(transCallback, errorCallback, successCallback){
   
		var db = this;
		this.getStorageDriver().requestTransaction(function(){
			transCallback.apply(window, [new HPI.SQLTransaction(db)]);
		});
		
    }

   ,getStorageDriver : function(){
   
		return this._storageDriver;
		//return new HPI[this.cfg.storageDriver](this.cfg);
    }
	
	//performs a manual initialisation of the database
   ,mInit : function(data, success, error){

        this.getStorageDriver()._mInit(data, success, error);   
   }


});


//shorthand function
HPI.SQLDatabase.open = function(cfg){
	return new HPI.SQLDatabase(cfg);
}

//introduce the standard class
if( 'undefined' == typeof SQLDatabase ){
	SQLDatabase = HPI.SQLDatabase;
}

if( 'undefined' == typeof openDatabase ) {
	
	window["INVALID_STATE_ERR"] = undefined;
	
    window["storageDriverToUse"] = function () {

        //return window["DriverToUse"] || "LocalStorage";
        //return "IndexedDB";
        return ((window.indexedDB || window.mozIndexedDB || window.webkitIndexedDB || window.msIndexedDB) ? "IndexedDB" : "LocalStorage");
    }

    window["openDatabase"] = function(shortName, version, displayName, maxSize) {
        
		var storageDriver = storageDriverToUse();
		
		return HPI.SQLDatabase.open({
            shortName: shortName
		   , dbName: shortName
		   , version: version
		   , displayName: displayName
		   , maxSize: maxSize
		   , storageDriver: storageDriver
        });
		
    }
	
}

function NoWebSQL(){
    return typeof storageDriverToUse == 'function';
}

//storage driver to be used with HPI.Database
HPI.AbstractWebStorage = Class.extend({

	
    requestTransaction : function(sqlTrxnCallback){

		sqlTrxnCallback.apply(window, []);
	}
	
	//returns the standard structure of the db for nonWebSQL drivers
   ,dbStructure : function(){
   
		return  {
			tables : {}
		}
   
   }
   
   ,infoSchema : function(){
   
		return  {
			tables : {}
		};

   }
   
	,_proc : function(parseTree, result, cbSuccess, cbError, sqlTrxn){
	
		var __proc = function(){
		
			var pt = parseTree;
		
			try{
				switch(true){
				
					case pt.target !== undefined : {
						
						var name = pt.target.value;
						
						//create or drop tbl
						if(pt.fieldDef || pt.isDrop == true){
							if( pt.isDrop == true ){						
								this._dropTable(name, pt, {cbSuccess : cbSuccess, cbError : cbError}, sqlTrxn);
							}
							else{
								this._createTable(name, this._cleanCreateTableTree(pt), {cbSuccess : cbSuccess, cbError : cbError}, sqlTrxn);
							}
						}
						//insert
						else if(pt.insertExpression){
							this._insertToTable(name, pt, {cbSuccess : cbSuccess, cbError : cbError}, sqlTrxn);
						}
						//update
						else if( pt.assignList ){
							this._updateTableData(name, pt, {cbSuccess : cbSuccess, cbError : cbError}, sqlTrxn);
						}
						//delete
						else{
							this._deleteFromTable(name, pt, {cbSuccess : cbSuccess, cbError : cbError}, sqlTrxn);
						}
						break;

					}
					
					//select statement
					default : {
					
						var _r = this._procRelExp(pt) || {};
						_r = this._toOrigForm(_r);
						HPI.apply(_r, {rowsAffected : 0, insertId : undefined});
						HPI.apply(result, _r);						
						if( 'function' == typeof cbSuccess ){
							cbSuccess.apply(window, [sqlTrxn, new HPI.SQLResultSet(result)]);
						}
						
					}
				
				}				
			}
			catch(e){
				if ('function' == typeof cbError) {
					if (e instanceof HPI.SQLError) {
	                    cbError.apply(window, [sqlTrxn, e]);
	                }
					else{
						throw e;
					}
				}
			}
		}
	
		this._prepare(__proc, this);
		
	}
	
	,_prepare  : function(onReady, scope){
		onReady.apply(scope, []);		
	}
	
	//transforms the result of _procRelExp to "original form" (i.e.: field names to its original specified casing)
	,_toOrigForm : function(result){
	
		var data  = result.data
		    ,_data = []
		    ,fields = result.fields;
			
		for (var i = 0, len = data.length; i < len; i++){
		
			var row = data[i], _row = {};
			for(var fieldName in fields){
				var field = fields[fieldName];
				if( field.isAmb) continue;
				var name = field.pubName || field.nameOrig;
				if( undefined == _row[name] ){
					//_row[name] = row[field.mapping];
					//this is the style used by WebSQL Results row (i.e., it is read-only)
					Object.defineProperty(_row, name, {
					    configurable : false
					   ,enumerable : true
					   ,writable : false
					   ,value : row[field.mapping]
					});
				}
			}
			
			_data = _data.concat([_row]);
			
		}
		
		return {
			data : _data
		};
		
	}
	
	,_relOperators : [
		'project'
		,'join'
		,'union'
		,'restrict'
		,'group_by'
		,'final_project'
		,'order_by'
		,'limit'	
	]
	
	//process select statements
	,_procRelExp : function(parseTree, prevResult){

		//arranged according to precedence
		var operators = this._relOperators
			,i 			    = 0
		    ,currOp      = ''
		    ,opLen       = operators.length
			,result 	    = prevResult || {}
		   
		while( i < opLen ){
		
			var  opArgs = [];
			
			currOp = operators[i];
			
			i++;
			
			switch( currOp ){

				case 'project' : {
					
					if( parseTree.source.select ){
						
						result = this._procSQFields(this._procRelExp(parseTree.source.select, result));
						continue; //so that other operators will not be called anymore
					}
					else{
						opArgs = opArgs.concat([parseTree.source]);
					}
					
					break;
				
				}
				
				case 'join' : {
				 
					if( parseTree.joins.length == 0 ) continue;
					
					var joins = [result];
					HPI.each(parseTree.joins, function(idx, join, items){
						
						if( join.right instanceof SQLParser.nodes.SubSelect ){
							throw new HPI.SQLError("Join subqueries not supported yet.");
						}
						
						joins = joins.concat(join);
						
					}, this);
					
					opArgs = [{joins: joins, where : parseTree.where, parseTree : parseTree}];
					
					break;
					
				}
				
				//case 'union' : {}
				
				case 'restrict' : {
					if( !parseTree.where ) continue;

					opArgs = [HPI.apply(result, {where : parseTree.where})]
					
					break;
				}
				
				case 'group_by'  : {
				
					opArgs = [HPI.apply(result, {group : parseTree.group, specFields : parseTree.fields})];
					
					break;					
				}
				
				case 'final_project' : {					 
					opArgs = [HPI.apply(result, {specFields : parseTree.fields, distinct : parseTree.distinct, autoColName : parseTree.autoColName})];
					break;
				}
				
				case 'order_by' : {
					
					if( parseTree.order == null ) continue;
					
					opArgs = [HPI.apply(result, {order : parseTree.order})];
					
					break;
				}
				
				case 'limit' : {
					
					if( parseTree.limit == null) continue;
					
					opArgs = [HPI.apply(result, {limit : parseTree.limit})];
					
					break;
				}
				
			}
			
			if( this[currOp] ){
				result = this[currOp].apply(this, [opArgs]);
			}
			
		}

		return result;
		
	}
	
    ,_cleanCreateTableTree : function(pt){
		
		//todo: Implement checking of only 1 PK in table. with message
		//could not prepare statement (1 table "c" has more than one primary key)
		
		var _pt = {fields : {}, fIndex : [], fIndexOrigNames : {}, ifNotExists : pt.ifNotExists}
		    ,targetName = pt.target.value.toUpperCase()
			,pkField = ''
			,autoIncField = ''
			,nonNullFields = {};

		for(var idx = 0, len = pt.fieldDef.length; idx < len; idx++){
		
			var f       = pt.fieldDef[idx]
			    ,name = (f.field.value).toUpperCase();
			
			if( f.isPK ){
				if( pkField != '' ) {//PK is already defined
					throw new HPI.SQLError('could not prepare statement (1 table "' + pt.target.value + '" has more than one primary key)', HPI.SQLError.SYNTAX_ERR);
				}
				pkField = name;
			}
			
			if( f.isAutoInc ) {
				if( autoIncField != '' ){//auto inc field already defined
					throw new HPI.SQLError('could not prepare statement (1 near "autoincrement": syntax error)', HPI.SQLError.SYNTAX_ERR);
				}
				autoIncField = name;
			}
			
			_pt.fields[name] = {
				type : f.type.value
			   ,name : name
			   ,nameOrig : f.field.value
			   ,alias : f.field.value
			   ,aliasOrig : name
			   ,ordPosition : idx
			   ,isAmbiguous : false
			   ,isAutoInc  : f.isAutoInc
			   ,isNullable : f.isNullable
			   ,isPK : f.isPK
			   ,mapping : name			   
			};
			
			_pt.fIndex[idx] = name;
			_pt.fIndexOrigNames[name] = f.field.value;
			
			if(  f.isNullable == false ){
				nonNullFields[name] = true;
			}
			
		}
		
		
		_pt = HPI.apply(_pt, {
			pkField : pkField
		   ,keys : {}
		   ,autoIncField : autoIncField
		   ,nonNullFields : nonNullFields
		});

		
		return _pt;

   }

 	
	//abstract
	//,_dropTable : function(name, args){}
	
	//process fields of subselects (used in derived tables)
	,_procSQFields : function(args){
	
		return args[0];
	}
	
	,supportedOperators : {
		
		'==' : function(v1, v2){
			return HPI.isEqual(v1, v2, true, true);
		}
		
	   ,'=' : function(v1, v2){
			return HPI.isEqual(v1, v2, true, true);
		}
	
	   ,'>' : function(v1, v2){
			return v1 > v2;
	   }
	   
	   ,'<' : function(v1, v2){
	   
			return v1 < v2;
	   }
	   
	   ,'<=' : function(v1, v2){
			return v1 <= v2;
	   }
	   
	   ,'>=' : function(v1, v2){
	   
			return v1 >= v2;
	   }
	   
	   ,'<>' : function(v1, v2){
	   
			return v1 != v2;
	   }
	   
	   ,'!=' : function(v1, v2){
		
			return v1 != v2;
	   }
	   
	   ,'+' : function(v1, v2){
			return v1 + v2;
	   }
	   
	   ,'AND' : function(v1, v2){
			return v1 === true && v2 === true;
		}
		
	   ,'OR' : function(v1, v2){
	   
			return v1 === true || v2 === true;
	    }
	   
	   ,'IN' : function(v, set, correlatedSet){

			//set clause is an enumerated list.
			//@todo: handle expressions in the enumeration (including, expressions with field references)
			if( HPI.isArray(set) ){
			
				var  _set = [];
				HPI.each(set, function(idx, item, items){
					_set[idx] = item.value;
				}, this);
				
				return HPI.inArray(v, _set, true, true);
			
			}
	
	
			//if SubQuery
			var _rel = HPI.apply({}, set);

			_rel.joins = _rel.joins.concat(HPI.apply(correlatedSet, {
				side : 'left'
			   ,mode : 'inner'
			   ,conditions : false
			   ,isSubQuery : true
			}));
	
			var result = this._procRelExp(_rel);
			
			if( HPI.count(result.specFields) > 1 ){
				throw new HPI.SQLError("could not prepare statement (1 only a single result allowed for a SELECT that is part of an expression)", HPI.SQLError.SYNTAX_ERR);
			}
			
			var fName = function(){
				var _f = '';
				HPI.each(result.fields, function(idx, item, items){
					_f = item.mapping;
				}, this);
				return _f;
			}.apply(this, []);
			
			var _v = {};
			_v[fName] = v;
			
			return HPI.inArray(_v, result.data, true, true);
	
	    }
		
	   ,'IFNULL' : function(v1, v2){
			return this._ifNull(v1, v2);
		}
		
	   ,'CAST' : function(v, toType){
			return this._cast([{val : v, toType : toType}]);
		}
	
	   ,'MAX' : function(expr, tuple, fields, allData){
	   
			var max = null
			    ,_max = 0
				,tuple = {}
				,exp = expr[0];

			for(var i = 0, len = allData.length; i < len; i++){
				tuple = allData[i];
				_max = this._evalExpInRel(exp, tuple, fields, allData, true);
				max = i == 0 ? _max : (
					max < _max ? _max : max
				);
			}
			
			return max;
	   }
	   
	   ,'MIN' : function(expr, tuple, fields, allData){
	   
			var min = allData.length > 0 ? 0: null
			    ,_min = 0
				,tuple = {}
				,exp = expr[0];

			for(var i = 0, len = allData.length; i < len; i++){
				tuple = allData[i];
				_min = this._evalExpInRel(exp, tuple, fields, allData, true);
				min = i == 0 ? _min : (
					min > _min ? _min : min
				);
			}
			
			return min;
	   }	   
	   
	   ,'COUNT' : function(expr, tuple, fields, allData){
	   
			return allData.length;
			
	   }
	   
	   ,'DATETIME' : function(dateStr){
		
			var formats = {
			
				//YYYY-MM-DD
				"1" : /^(\d{4})-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])!*$/g

				//YYYY-MM-DD HH:MM
				,"2" : /^(\d{4})-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01]) ([01][0-9]|2[0-3]):([0-5][0-9])!*$/g
				
				//YYYY-MM-DD HH:MM:SS or YYYY-MM-DD HH:MM:SS.SSS
			   ,"3" : /^(\d{4})-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01]) ([01][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])?(!*$|.[000-999]{3}!*$)/g

			   //YYYY-MM-DDTHH:MM
			   ,"4" : /^(\d{4})-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])T([01][0-9]|2[0-3]):([0-5][0-9])!*$/g
			   
			   //YYYY-MM-DDTHH:MM:SS or YYYY-MM-DDTHH:MM:SS.SSS
			   ,"5" : /^(\d{4})-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])T([01][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])?(!*$|.[000-999]{3}!*$)/g

			   //HH:MM
			   ,"6" : /^([01][0-9]|2[0-3]):([0-5][0-9])!*$/g
			   
			   //HH:MM:SS or HH:MM:SS.SSS
			   ,"7" : /^([01][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])?(!*$|.[000-999]{3}!*$)/g
			   
			   
			   //NOW
			   ,"NOW" : /^now!*$/gi
			   
			   //DDDDDDDDDD
			   ,"TIMESTAMP" : /^\d{10}!*$/g

			   
			};

			var getDate = function(seconds) {
   
			   var date = seconds ? new Date(seconds*1000) : new Date();
   
			   var yyyy = date.getFullYear().toString();

			   var mm = (date.getMonth()+1).toString(); // getMonth() is zero-based
			   mm = (mm[1]?mm:"0"+mm[0])

			   var dd  = date.getDate().toString();
			   dd = dd[1]?dd:"0"+dd[0];
			   
			   var hh  = date.getHours().toString();
			   hh = hh[1]?hh:"0"+hh[0];
				  
			   var min = date.getMinutes().toString();
			   min = min[1]?min:"0"+min[0];

			   var sec = date.getSeconds().toString();
			   sec = sec[1]?sec:"0"+sec[0];
			   
			   return yyyy + '-' + mm  + '-' + dd + " " + hh + ":" + min + ":" + sec;
			  
			};
			
			var matched = '';
			for(var key in formats){
			
				if( null == formats[key].exec(dateStr) ){
					continue;
				}
			
			
				//we have a match
			
				matched = key;
				
				if( !(matched == "NOW" || matched == "TIMESTAMP") ){
				
					if( dateStr.indexOf(".") != -1 ){
						dateStr = dateStr.split(".")[0];
					}
					
				}
				else{
					
					if( matched== 'NOW' ){
						dateStr = getDate();
					}
					else{
						dateStr = getDate(dateStr);
					}
					
				}
				
				
				break;
				
			}

			return matched ? dateStr : null;
			
	   }
	   
	   ,'LIKE' : function(string, pattern){
			
			string = string.toLowerCase();
			pattern = pattern.toLowerCase();
			
			pattern = pattern.replace(/%/g, ".*");
			var re = new RegExp('^' + pattern + '?$', "g");			
			return re.test(string);

	   }
	   
	   ,'IS' : function(v1, v2){
	   
			if( v1 == null || v2 == null ){
				return v1 == v2;
			}
			
			return HPI.isEqual(v1, v2);
	   }

	   ,'IS NOT' : function(v1, v2){
	   
			if( v1 == null || v2 == null ){
				return v1 != v2;
			}
			
			return !HPI.isEqual(v1, v2);
	   }
	   
	}

	,aggOperators : {'MAX' : true, 'MIN' : true, 'COUNT' : true}
	
	,_procSubQuery : function(subQuery, correlatedSet){
	
		if( subQuery.fields > 1 || subQuery.fields[0].star ) {
			throw new HPI.SQLError("could not prepare statement (1 only a single result allowed for a SELECT that is part of an expression)", HPI.SQLError.SYNTAX_ERR);
		}
		
		//if SubQuery
		var _rel = HPI.apply({}, subQuery);

		_rel.joins = _rel.joins.concat(HPI.apply(correlatedSet, {
			side : 'left'
		   ,mode : 'inner'
		   ,conditions : false
		   ,isSubQuery : true
		}));

		var result = this._procRelExp(_rel);
		
		var v = null;
		
		//the first row will be the result
		if( result.data.length > 0 ){
		
			HPI.each(result.data[0], function(_idx, _val, _data){
				v = _val;
				return false;
			}, this);

		}
		
		
		return v;

	}
	
	//evaluate expressions in the context of relational expressions (i.e., mostly used in join and where expressions)
	,_evalExpInRel : function(exp, tuple, fields, allData, isInAggOp){
	
		allData      = allData || [];
		isInAggOp = isInAggOp || false; //whether we are in the middle of an aggregate operator
		
		var expArgs = [] // expression arguments	
			,op = '' //if this remains blank, meaning it's a literal or attr. reference
			,isSubQuery = false;
			
		switch(true){
		
			case ( exp instanceof SQLParser.nodes.Op ) : {
				
				op = exp.operation.toUpperCase();
			
				expArgs = [exp.left, exp.right];
				
				break;
				
			}
			
			case ( exp instanceof SQLParser.nodes.FunctionValue ) : {
				op        = exp.name.toUpperCase();
				expArgs = exp.arguments;
				
				break;
			}
			
			case ( exp instanceof SQLParser.nodes.FunctionCastValue ) : {
				op = exp.name;
				expArgs = [exp.expression, exp.toType.value];
				break;
			}

			case (exp instanceof SQLParser.nodes.SubQuery ) : {
				
				isSubQuery = true;
				expArgs = [exp, { data : [tuple], fields : fields }];
			}
			
		}

		if( op == 'IN' ){
			if( exp.right instanceof SQLParser.nodes.SubQuery ){
				expArgs = expArgs.concat([{ data : [tuple], fields : fields }]);
			}
			expArgs[0] = this._evalExpInRel(expArgs[0], tuple, fields, allData, isInAggOp);
			return this.supportedOperators[op].apply(this, expArgs);
		}
		
		if( isSubQuery ){
			return this._procSubQuery.apply(this, expArgs);
		}
		
		//an attribute reference or a (true) literal was encountered.
		if( op == '' ){
			
			return undefined !== exp.values ? 
					  //field reference
					  function(){

						var name = exp.values.join(".")
						    ,f = fields[name.toUpperCase()];
						
						if(!f){
							throw new HPI.SQLError("could not prepare statement (1 no such column: " + name + ")", HPI.SQLError.SYNTAX_ERR);
						}
						
						if( f.isAmb === true ){
							throw new HPI.SQLError("could not prepare statement (1 ambiguous column name:" + name + ")", HPI.SQLError.SYNTAX_ERR);
						}
						
						return tuple[f.mapping];

					  }() : 
					  //the true literal
					  (exp.value === undefined ? exp : exp.value);
						
		}

		if( this.supportedOperators[op] == undefined ){
			throw new HPI.SQLError("Operator/function '" + op + "' is not yet supported.");
		}

		if( this.aggOperators[op] ){
			if( isInAggOp ){
				throw new HPI.SQLError("could not prepare statement (1 misuse of aggregate function " + op + ")", HPI.SQLError.SYNTAX_ERR);
			}
			return this.supportedOperators[op].apply(this, [expArgs, tuple, fields, allData]);
		}
		
		var _expArgs = [];
		for(var i = 0, len = expArgs.length; i < len; i++){
		
			_expArgs = _expArgs.concat([
				this._evalExpInRel(expArgs[i], tuple, fields, allData, isInAggOp)
			]);

		}

		return this.supportedOperators[op].apply(this, _expArgs);
		
	}
	
	//process cast
	,_cast : function(args){
	
		var knownTypes = {
				'INT' : function(v) { return this._parseValue(v, 'int'); }
			   ,'INTEGER' : function(v) { return this._parseValue(v, 'integer'); }
			   ,'TEXT' : function(v) { return v.toString(); }
			}
		   ,arg 	  = args[0]
		   ,val	  = arg.val
		   ,toType = arg.toType.toUpperCase();
		   
		var fn = knownTypes[toType];  
		if( undefined ==  fn){
			throw new HPI.SQLError("Unknown type '" + toType + "' used in CAST");
		}
		
		//try native
		
		
		return knownTypes[toType].apply(this, [val]);
		
	}
	
	,_ifNull : function(val, subVal){
	
		return val != null ? val : subVal;
	}
	
	//only used on table references and "setup" the correct meta.
	,project : function(args){

		var arg	  = args[0]
		    ,name = arg.name.value.toUpperCase()
		    ,alias  = (arg.win ? arg.win.value.toUpperCase() : name)
			,fields  = function(){
				
				var _fields  = this.tblInfo(name, 'columns')
					,fields_  = {};

				HPI.each(_fields, function(fName, field, allFields){
					
					var fullName = alias + "." + fName;
					field = HPI.apply(field, {fullName : fullName});
				
					fields_[fName] = field;
					fields_[fullName] = field;
				
				}, this);
				
				return fields_;
				
			}.apply(this, []);
		
		return {		
			data  : this.readFromStorage(name)
		   ,fields : fields
		   ,name : name
		   ,alias  : alias		
		};

	
	}

	,join :  function(args){
		
		var arg   = args[0]
		    ,joins = arg.joins
			,_row = {} //tuple
			,_data = []
			_fields = {}
			,r =  {} //will always contain the intermediate result
			,_r = {} //the current relation, will always be of type SQLParser.nodes.Join, and thus source of meta info (e.g., join condition and side -- left or right) for the JOIN operation
			
		var fieldsCombine = function(fields1, fields2, rel2IsSubquery){
		
			rel2IsSubquery = rel2IsSubquery || false;
			
			fields1 = HPI.apply({}, fields1);
			fields2 = HPI.apply({}, fields2);
			
			var editedFields = {};
			
			for(var fName in fields2){
			
				var f1 = fields1[fName];
				
				if( f1 == undefined ) continue;

				var f2 = HPI.apply({}, fields2[fName]);
				f1 = HPI.apply({}, f1); 
				if( rel2IsSubquery == false ){
				
					if( f1.fullName == f2.fullName ) {
						throw new HPI.SQLError("could not prepare statement (1 ambiguous column name:" + f2.fullName + ")", HPI.SQLError.SYNTAX_ERR);
					}
					
					if( !f1.isAmb ) {
						delete fields1[fName];
						editedFields[fName] = {isAmb : true};
						fields1[f1.fullName] = HPI.apply(f1, {mapping : f1.fullName});
					}
					
					delete fields2[fName];					
					fields2[f2.fullName] = HPI.apply(f2, {mapping : f2.fullName});
					
				}
				else{
					//need to preserve the old mapping
					editedFields[f2.fullName] = HPI.apply(HPI.apply({}, f2), {mapping : f2.fullName});
					delete fields2[fName];
				}
				
			}

			return HPI.apply(HPI.apply(HPI.apply({}, fields2), fields1), editedFields);
	
		}
		
		var rowCombine = function(rel1, rel2, rel2IsSubquery){
			
			rel2IsSubquery = rel2IsSubquery || false;
			
			var row1 = HPI.apply({}, rel1.row)
		        ,row2 = HPI.apply({}, rel2.row);
			
			for(var fName in rel2.fields){
			
				var f = rel1.fields[fName];
				if( f == undefined ) continue;
				
				
				if( rel2IsSubquery == false ){
					var v1 = row1[fName]
						,v2 = row2[fName];
					if( !f.isAmb && v1 !== undefined ) {
						delete row1[fName];
						row1[rel1.fields[fName].fullName] = v1;
					}
					
					delete row2[fName];				
					row2[rel2.fields[fName].fullName] = v2;
				}
				else{
					row2[rel2.fields[fName].fullName] = row2[fName];
					delete row2[fName];				
				}
			}
			
			return HPI.apply(
				HPI.apply(
					{}
				   ,row1
				)
			   ,row2
			);

		}
		
		var preRestrict = function(rel, conditions, origParseTree){
			
			var transformCondition = function(cond, origPT, prevOp, transformMeta){

				var _cond       = cond
					,recurseOp  = {"AND" : true, "OR" : true}
					,_prevOp    = prevOp || 'AND'
					,op 	         = cond.operation.toUpperCase()
					,left 			 = cond.left
					,right 		 = cond.right
					,STRING	 	 = SQLParser.nodes.StringValue
					,NUMBER    = SQLParser.nodes.NumberValue
					,BOOL    	 = SQLParser.nodes.BooleanValue
					,FIELD_REF = SQLParser.nodes.LiteralValue
					
				if( !recurseOp[op] ){
				
					
					var operand1 = left instanceof FIELD_REF ? left : right;
					var operand2 = operand1 === left ? right : left;
					if(
						 !(operand2 instanceof STRING || operand2 instanceof NUMBER || operand2 instanceof BOOL) ||
						  ((operand1 instanceof FIELD_REF) && !rel.fields[operand1.values.join(".").toUpperCase()])
					){

							switch(_prevOp){
							
								case 'AND' : {
									_cond = new SQLParser.nodes.Op('=', 1, 1); 
									break;
								}

								case 'OR' : {
									_cond = new SQLParser.nodes.Op('=', 1, 0);
									break;
								}
								
							}
							
					}
					else{
						transformMeta.hasRestrict = true;
						_cond = new SQLParser.nodes.Op(_cond.operation, _cond.left, _cond.right);
					}


				}
				else{
				
					//_cond.left   = transformCondition.apply(this, [left, origPT, op, transformMeta]);
					//_cond.right = transformCondition.apply(this, [right, origPT, op, transformMeta]);
					_cond = new SQLParser.nodes.Op(
						_cond.operation
					   ,transformCondition.apply(this, [left, origPT, op, transformMeta])
					   ,transformCondition.apply(this, [right, origPT, op, transformMeta])
					);  
					
				}

				return _cond;
				
			}
			
			var transformMeta = {hasRestrict : false};
			var _cond = transformCondition.apply(this, [conditions, origParseTree, undefined, transformMeta]);
			
			if( transformMeta.hasRestrict ){
				rel = this.restrict([HPI.apply({where : {conditions : _cond}}, rel)]);
			}
			
			return rel;
			
		}
		
		for(var i = 0, len = joins.length; i < len; i++){
			
			_r = joins[i];
			
			//if first iteration, then do not process process further. assign the relation from the FROM clause as the intermediate relation.
			if( i == 0 ){
				r = _r;	
				continue;
			}
			
			
			var _side = _r.side.toUpperCase() //possible sides: LEFT or RIGHT
			    ,jt       = _r.mode.toUpperCase() //join type: INNER or OUTER
			    ,_r1    =  _side == 'LEFT' ? r    : _r
			    ,_r2    =  _side == 'LEFT' ? _r  : r
			    ,cond  = _r.conditions;
			
			_r1 = (_r1 instanceof SQLParser.nodes.Join) ? this.project([_r1.right]) : _r1;
			_r2 = (_r2 instanceof SQLParser.nodes.Join) ? this.project([_r2.right]) : _r2;
			
			
			_fields = fieldsCombine.apply(this, [
				_r1.fields
			   ,_r2.fields
			   ,_r2.isSubQuery
			]);
			
			//@todo: handle cases when this function is used in subqueries
			if( jt == 'INNER' && !_r2.isSubQuery && arg.where ) {
				_r1 = preRestrict.apply(this, [_r1, HPI.apply({}, arg.where.conditions), arg.parseTree]);
				_r2 = preRestrict.apply(this, [_r2, HPI.apply({}, arg.where.conditions), arg.ParseTree]);
			}
			
			if( jt == 'INNER' && cond ){
				_r1 = preRestrict.apply(this, [_r1, HPI.apply({}, cond), arg.parseTree]);
				_r2 = preRestrict.apply(this, [_r2, HPI.apply({}, cond), arg.ParseTree]);
			}
/*
			var index = ('sssurveycode').toUpperCase();
			index = index.toUpperCase();
			if( index != '' ){
			
				var hash = {};
				
				for(var j = 0, jLen = _r2.data.length; j < jLen; j++){
					var row = _r2.data[j];
					
					if( hash[row[index]] ){
						hash[row[index]] = hash[row[index]].concat([row]);
						continue;
					}
					
					hash[row[index]] = [row];
				}
				
				var indexed2 = ('surveycode').toUpperCase();
				for(var j = 0, jLen = _r1.data.length; j < jLen; j++){
					
					var row1 = HPI.apply({}, _r1.data[j]), hasMatch = false
					,rows = hash[row1[indexed2]];
					if( rows ){
						for(var k = 0, kLen = rows.length; k < kLen; k++){
							var row2 = rows[k];
							_row = rowCombine.apply(this, [
								{row : row1, fields : _r1.fields}
							   ,{row : row2, fields : _r2.fields}
							   ,_r2.isSubQuery
							]);
							
							_data = _data.concat([_row]);
						}
					}
				}
				
				r = {
					data : _data
				   ,fields : _fields
				};
				return r;
			}
*/			
			for(var j = 0, jLen = _r1.data.length; j < jLen; j++){
			
				var row1 = HPI.apply({}, _r1.data[j]), hasMatch = false, row2 = {};
				
				for(var k = 0, kLen = _r2.data.length; k < kLen; k++){
				
					row2 = HPI.apply({}, _r2.data[k]);
					
					_row = rowCombine.apply(this, [
						{row : row1, fields : _r1.fields}
					   ,{row : row2, fields : _r2.fields}
					   ,_r2.isSubQuery
					]);
						
					if( !cond || this._evalExpInRel(cond, _row, _fields) === true ){
						_data = _data.concat([_row]);
						hasMatch = true;
					}                    

				}
				
				if( jt == 'OUTER' && hasMatch == false ){

					//process the null fields
					HPI.each(_r2.fields, function(idx, item, items){
						row2[idx] = null;
					}, this);
					
					_row = rowCombine.apply(this, [
						{row : row1, fields : _r1.fields}
					   ,{row : row2, fields : _r2.fields}
					   ,_r2.isSubQuery
					]);
					
					_data = _data.concat([_row]);
					
				}
				
			}
			
			
			r = {
				data : _data
			   ,fields : _fields
			};
			
			_data = [];
			
		}

		return r;
		
	}

	,restrict : function(args){
	
		var arg    = args[0]
		    ,data  = arg.data
			,_data = [];

		for(var i = 0, len = data.length; i < len; i++){
		
			var tuple = data[i];
			if( this._evalExpInRel(arg.where.conditions, tuple, arg.fields) === true ){
				_data = _data.concat([tuple]);
			}
			
		}

		return HPI.apply(arg, {
			data : _data
		});

	}	
	
	,group_by : function(args){
	
		
		var arg = args[0]
		    ,specFields = arg.specFields
			,groupBy = arg.group
			,hasAgg = false;
		
		var expHasAggFn = function(exp){
			
			var args = [];
			switch(true){
			
				case (exp instanceof SQLParser.nodes.FunctionValue)
					|| (exp instanceof SQLParser.nodes.FunctionCastValue) : {
					
					if( this.aggOperators[exp.name.toUpperCase()] ){
						return true;						
					}
					args = exp.arguments;
					break;
				}
				
				case exp instanceof SQLParser.nodes.Op : {
					args = [exp.left, exp.right];
					break;
				}
				
				default : {
					return false;
				}
				
			}
			
			var arg = null, result = false;
			for(var i = 0, len = args.length; i < len; i++){
				arg = args[i];
				result = result || expHasAggFn.apply(this, [arg]);
				if( result ) {
					return true;
				}
			}
		
			return false;
			
		}
		
		HPI.each(specFields, function(idx, item, items){
			hasAgg = expHasAggFn.apply(this, [item.field]);
			if( hasAgg ){
				return false;
			}			
		}, this);

		//has agg. function specified
		if( hasAgg == false ){
			delete arg.specFields;
			return arg;
		}
		
		//no group by clause
		if( groupBy == null){
			arg = HPI.apply(arg, {
				data : [arg.data[arg.data.length-1]]
			   ,groupings : [arg.data]
			});
		}
		//todo: has group by
		else{
			
		}
		
		return arg;
		
	}
	
	,final_project : function(args){
		
		var arg = args[0]
			,specFields = arg.specFields //specified fields
			,fields = arg.fields
			,_fields = {}
			,fields_ = []
			,data  = arg.data
			,data_ = []
			,autoColName = arg.autoColName || false
			,colNames = {}; //used when autoColName is TRUE

		//build the fields that we will work with
		for(var i = 0, len = specFields.length; i < len; i++){
			
			var field = specFields[i];
			

			
			if( field.star === true  ){
				if( field.literal === '' ){
					fields_ = fields_.concat([{isStar : true}]);
				}
				else{
				
					var _alias = field.literal.value.toUpperCase(), addedFields = {};
					
					HPI.each(fields, function(fName, _field, allFields){
					
						var _name   = _field.name
							,_fName  = _alias + "." + _name;
						
						//add the fully qualified field
						if( undefined != fields[_fName] && !addedFields[_fName] ){
						
							fields_ = fields_.concat([{
								name : _fName
							   ,expr : new SQLParser.nodes.LiteralValue({values : [_alias]}, _name)
							   ,pubName : _field.nameOrig
							}]);
							
							addedFields[_fName] = true;
							
							//add the non-fully qualified field
							if( undefined != fields[_name] && !addedFields[_name] ){					
								fields_ = fields_.concat([{
									name :_name
								   ,expr : new SQLParser.nodes.LiteralValue(_name)
								   ,pubName : _field.nameOrig
								}]);
								addedFields[_name] = true;
							}
							
						}
						
					}, this);
					
				}
				
				continue;
			}
			
			var				
				pubName  = function(){

					switch(true){
					
						case field.name instanceof SQLParser.nodes.LiteralValue : {
							return field.name.values[field.name.values.length-1];
						}
						
						case field.name instanceof SQLParser.nodes.StringValue : {
							//needs to prepend a name because in IE objects get sorted if having a numeric prop name.
							//return 'field_' + field.name.value.toString();
                            return field.name.value.toString();
						}						
		
						default : {
							//needs to prepend a name because in IE objects get sorted if having a numeric prop name.
							return 'field_' + field.name.toString();
						} 
						
					}
					
			   }()

			   ,sysfullName  = function(){
					
					if( (field.field instanceof SQLParser.nodes.LiteralValue) ) {
						return field.field.values.join(".");
					}
					
					return pubName;					
					
				}().toUpperCase();
			
			if( field.field instanceof SQLParser.nodes.LiteralValue ) {
				var _f = fields[sysfullName];
				if( undefined == _f ){
					throw new HPI.SQLError("could not prepare statement (1 no such column: " + pubName + ")", HPI.SQLError.SYNTAX_ERR);
				}
				
				if( _f.isAmb == true ){
					throw new HPI.SQLError("could not prepare statement (1 ambiguous column name:" + pubName + ")", HPI.SQLError.SYNTAX_ERR);
				}
			}
			
			if( autoColName ){
				if( colNames[pubName] ){
					pubName = pubName + ":1";
					sysfullName = sysfullName + ":1";
				}
				colNames[pubName] = true;
			}
			
			fields_ = fields_.concat([{
				name : sysfullName
			   ,pubName : pubName
			   ,expr : field.field
			}]);
			
		}
		
		//build the rows using the fields specified
		if ( data.length == 0 ){
		
			for(var j = 0, fLen = fields_.length; j < fLen; j++){
				
				var f_ = fields_[j];
				switch(true){
				
					case f_.isStar : {
						_fields = fields;
						break;
					}
					
					case f_.expr instanceof SQLParser.nodes.LiteralValue : {
						var f = fields[f_.name];
						_fields[f_.name] = HPI.apply({pubName : f_.pubName}, f);
						break;
					}
					
					//expression...
					default : {
						_fields[f_.name] = f_;
					}
					
				}
			
			}
			
		}
		else{
		
			for(var i = 0, len = data.length; i < len; i++){
			
				var row = data[i], row_ = {};
				
				for(var j = 0, fLen = fields_.length; j < fLen; j++){
					
					var f_ = fields_[j];
					
					switch(true){
					
						case f_.isStar : {
							row_   = HPI.apply(row_, row);
							_fields = fields;
							break;
						}
						
						
						case f_.expr instanceof SQLParser.nodes.LiteralValue : {
							
							var f = fields[f_.name];
							row_[f.mapping] = row[f.mapping];
							_fields[f_.name] = HPI.apply({pubName : f_.pubName}, f);
							break;
							
						}
						
						//expression...
						default : {
						
							row_[f_.name] = this._evalExpInRel(f_.expr, row, fields, arg.groupings ? arg.groupings[i] : []);
							_fields[f_.name] = HPI.apply({mapping : f_.name}, f_);

						}
						
					}				
					
				}
				
				if( arg.distinct == true ){
					if( HPI.inArray(row_, data_, false) ){
						continue;
					}
				}
				
				data_ = data_.concat([row_]);
			
			}
			
		}

		var ret = HPI.apply(arg, {
			data : data_
		   ,fields : _fields
		});

		return ret;
		
	}
	
	//process order by
	,order_by : function(args){
	
		var arg 		= args[0]
		    ,orderBy = arg.order.orderings
			,data     = arg.data
		
		if( data.length == 0 ){
		
			return arg;
		}

		var _data	= data //final result
			,crd       = HPI.count(data) - 1 //cardinality			
			,_s  		= [] //sorted
			,_sk      = []; // sort keys used
		
		for(var i = 0, len = orderBy.length; i < len; i++){
		
			var order = orderBy[i]
			    ,dir     = order.direction
                ,col     = order.collation || ''
				,o       = order.value //order expression
				,f        = undefined //name of field to be sorted
				,sf       = null //sort function to be used
				,fName = null;
			
			switch(true){
			
				case o instanceof SQLParser.nodes.LiteralValue : {
					fName = o.values.join(".");
					f = arg.fields[fName.toUpperCase()];
					var foundField = false;
					if( f == undefined ){
						var _fn = fName.toUpperCase();
						HPI.each(arg.fields, function(idx, item, all){
							if( item.mapping == _fn ){
								f = item;
								foundField = true;
								return false;
							}
						}, this);
						
						if( !foundField ){
							continue;
						}
						
					}
					
					break;
					
				}
				
				case o instanceof SQLParser.nodes.FunctionCastValue : {
				
					if( o.name.toUpperCase() != 'CAST' ){
						throw new HPI.SQLError("Illegal function '" + o.name + "' was used in ORDER BY clause.");
					}
					
					//if false, will execute the "default" handler
					if( 
							 (o.expression instanceof SQLParser.nodes.LiteralValue) 
						&& (o.toType instanceof SQLParser.nodes.LiteralValue)
					){
					
						fName = o.expression.value;
						f  		  = arg.fields[fName.toUpperCase()];
						
						sf = function(){
						
							var me = this
							    ,castType = o.toType.value;
								
							return function(v1, v2, dir){
							
								dir = dir.toUpperCase();
								
								var _v1 = me._cast([{val : v1, toType : castType}])
									,_v2 = me._cast([{val : v2, toType : castType}]);
							
								return dir == 'ASC' ? _v1 < _v2 : _v1 > _v2;
							
							}
							
						}.apply(this, []);
						
						break;
						
					}
				
				}
				
				default : {
					throw new HPI.SQLError("Unrecognized expression was used in ORDER BY clause.");
				}
				
			}
			
			if( f && f.isAmb === true ){
				throw new HPI.SQLError("could not prepare statement (1 ambiguous column name:" + fName + ")", HPI.SQLError.SYNTAX_ERR);
			}
			
			if( f == undefined ) {
				throw new HPI.SQLError("could not prepare statement (1 no such column: " + fName + ")", HPI.SQLError.SYNTAX_ERR);
			}
			
			var k  = f.mapping; //key
			_s     = HPI.sort(data, 0, crd, k, dir, sf, {collation: col});
			_data = _sk.length == 0 ? HPI.mergeSorted(_s, _data) : HPI.mergeSorted(HPI.keys(_data, _sk), _s);	
			_sk    = _sk.concat([k]);
			
		}
		
		return HPI.apply(arg, {
			data : _data
		});
		
	}
	
	//todo: handle scenario when pageNo/offset is specified
	,limit : function(args){
		
		var arg = args[0]
		    ,numOfRows = arg.data.length < arg.limit.numRows.value ? arg.data.length : arg.limit.numRows.value
			,_data = [];
		
		for(i = 0; i < numOfRows; i++){
			_data = _data.concat([arg.data[i]]);
		}
		
		
		return HPI.apply(arg,{
			data : _data
		});
		
	}
	
	//parse to actual data type; sometimes a value is string but must be saved as int. this function is for that.
	//@todo: to be refactored to CAST
	,_parseValue : function(v, toType){
	
		toType = toType !== undefined ? toType.toLowerCase() : toType;
		if( toType == 'real' || toType == 'int' || toType == 'integer' ){
			var _v = parseInt(v);
			return isNaN(_v) ? v : _v;
		}
		
		return v;
		
	}
	
	,_insertToTable : function(name, args, callbacks, sqlTrxn){
	
		var table = this._getDBObj(name, 'table');
		if( !table ){
			throw new HPI.SQLError("could not prepare statement (1 no such table: " + name + ")", HPI.SQLError.SYNTAX_ERR);
		}
		 
		var n        = name.toUpperCase()
			,tblSch = table.schema;

		var rows = []
		    ,tbl = {data : table.data}
			//if the field is in the parse tree, build the indexed fields; else, use the one from the schema
		    ,fields = args.fields ?
						(function(){
							var _f = [];
							HPI.each(args.fields, function(idx, item, items){
								_f = _f.concat([item.name.value.toUpperCase()]);
							});
							return _f;
						}()) : tblSch.fIndex
			,allFields = tblSch.columns;

		HPI.each(fields, function(idx, specName, fields){
			if( undefined === allFields[specName] ){
				throw new HPI.SQLError("No result. could not prepare statement (1 table " + n + " has no column named " + specName + ")", HPI.SQLError.SYNTAX_ERR);
			}
		}, this);
		
		if( HPI.isArray(args.insertExpression) ){
			var row_ = {};
			HPI.each(args.insertExpression, function(idx, item, items){
				row_[idx.toString()] = item.value;
			}, this);
			rows = rows.concat([row_]);
		}
		else if ( args.insertExpression instanceof SQLParser.nodes.Select ){
		
			var result = this._procRelExp(HPI.apply({autoColName : true}, args.insertExpression))
			rows  = result.data;			
		}
		
		if( HPI.count(rows) < 1 ){
			return;
		}
		
		var fldCount = HPI.count(fields), dataFldCount = HPI.count(rows[0]);
		if(  fldCount != dataFldCount ){
			throw new HPI.SQLError("could not prepare statement (1 " + dataFldCount + " values for " + fldCount + " columns)", HPI.SQLError.SYNTAX_ERR);
		}
			
		var _rowDefaults = {};
		
		HPI.each(allFields, function(idx, _field, _fields){
			_rowDefaults[idx] = null;
		}, this);
			
		var pkField 					= tblSch.pkField || ''
			 ,autoIncField 			= tblSch.autoIncField || ''
			 ,lastInsertId 			= tblSch.lastInsertId || 0
		     ,_keys 						= tblSch.keys || {}
			 ,_nonNullFields 		= tblSch.nonNullFields || {}
			 ,_nonNullFieldsCnt 	= HPI.count(_nonNullFields)
		     ,_rows 						= []
		     ,_row 						= {}
			 ,row_						= {};
		
		for(var i = 0, len = rows.length; i < len; i++){
		
			_row = {}
			row_ = rows[i];
			var j = 0;
			for(var fn in row_){
				//_row[fields[j]] = row_[fn];
				var f = allFields[fields[j]];
				_row[f.name] = this._parseValue(row_[fn], f.type);
				j++;
			}
			
			_row = HPI.applyIf(_row, _rowDefaults);
			
			if( _nonNullFieldsCnt > 0 ) {
			
				HPI.each(_nonNullFields, function(idx, _nnf, _nnfs){
					//if field is not an autoinc field, and it is null
					if ( autoIncField != idx && _row[idx] == null ){
						throw new HPI.SQLError("could not execute statement (19 constraint failed)", HPI.SQLError.DATABASE_ERR);
					}
				}, this);
				
			}
			
			if( autoIncField != '' ){
				if ( _row[autoIncField] == null ) {
					_row[autoIncField] = ++lastInsertId;
				}
			}
						
			if( pkField != '' ) {			
				if( _keys[_row[pkField]] != undefined ){
					throw new HPI.SQLError("could not execute statement (19 constraint failed)", HPI.SQLError.DATABASE_ERR);
				}
				
				lastInsertId = _row[pkField];
				_keys[_row[pkField]] = true;			
			}
			
			_rows = _rows.concat(_row);

		}
		
		table = HPI.apply(table, {
			data : tbl.data.concat(_rows)
		   ,schema : HPI.apply(table.schema, {
				keys : _keys
			   ,lastInsertId : lastInsertId
		   })
		});

		this._persistDBObj('table', table, {rowsAffected : _rows.length, insertId : lastInsertId}, callbacks, sqlTrxn);
		
	}
	
	,_updateTableData : function(name, args, callbacks, sqlTrxn){
	
		var table = this._getDBObj(name, 'table');
		if( !table ){
			throw new HPI.SQLError("could not prepare statement (1 no such table: " + name + ")", HPI.SQLError.SYNTAX_ERR);
		}

		var n    					= name.toUpperCase()
			,tblSch 				= table.schema
			,assignmentList = args.assignList;
			
		var allFields = tblSch.columns;
		for(var j = 0, jLen = assignmentList.length; j < jLen; j++){
			
			var op = assignmentList[j];
				
			if( !(op instanceof SQLParser.nodes.Op) || op.operation != '=') {
				throw new HPI.SQLError("Invalid assignment in SET clause.");
			}
			
			var specName = op.left.values.join(".").toUpperCase();
			if( allFields[specName] == undefined ){
				throw new HPI.SQLError("could not prepare statement (1 no such column: " + specName + ")", HPI.SQLError.SYNTAX_ERR);
			}
			
		}
		
		var pkField 					= tblSch.pkField || ''
			 ,autoIncField 			= tblSch.autoIncField || ''
			 ,lastInsertId 			= tblSch.lastInsertId || 0
		     ,_keys 						= tblSch.keys || {}
			 ,_nonNullFields 		= tblSch.nonNullFields || {}
			 ,_nonNullFieldsCnt 	= HPI.count(_nonNullFields)

			 ,condition					= args.updateCondition instanceof SQLParser.nodes.Where ? 
											    args.updateCondition.conditions : false
												
			 ,rowsToUpdate 			= []
			 ,_row						= {}
			 ,row_						= {}
			 ,_ra 						= 0;
		
		rowsToUpdate = table.data;//this.readFromStorage(name);

		for(var i = 0, len = rowsToUpdate.length; i < len; i++){
			
			row_ = rowsToUpdate[i];
			
			//has where clause and the where clause evaluated to FALSE
			if( condition != false && this._evalExpInRel(condition, row_, allFields) == false ) continue;
			
			_row = rowsToUpdate[i];
			
			for(var j = 0, jLen = assignmentList.length; j < jLen; j++){
				
				var op = assignmentList[j]
				    ,fName = op.left.value.toUpperCase()
					,newVal = this._evalExpInRel(op.right, row_, allFields);

				if( newVal == null && _nonNullFieldsCnt > 0 ){
					if( _nonNullFields[fName] ){
						throw new HPI.SQLError("could not execute statement (19 constraint failed)", HPI.SQLError.DATABASE_ERR);
					}					
				}
				
				if( fName == pkField ){
					if( _keys[newVal] != undefined ){
						throw new HPI.SQLError("could not execute statement (19 constraint failed)", HPI.SQLError.DATABASE_ERR);
					}
					delete _keys[_row[pkField]];
					_keys[newVal] = true;
				}
				
				
				if( fName == autoIncField ){
					//lastInsertId++;
					lastInsertId = newVal;
				}
				
				_row[fName] = newVal;
				
			}
			
			_ra++;
			rowsToUpdate[i] = _row;
			
		}
		
		table = HPI.apply(table, {
			data : rowsToUpdate
		   ,schema : HPI.apply(table.schema, {
				keys : _keys
			   ,lastInsertId : lastInsertId
		   })
		});
		
		this._persistDBObj('table', table, {rowsAffected : _ra, insertId : lastInsertId}, callbacks, sqlTrxn);
		
	}
	
	,_deleteFromTable : function(name, args, callbacks, sqlTrxn){

		var table = this._getDBObj(name, 'table');
		if( !table ){
			throw new HPI.SQLError("could not prepare statement (1 no such table: " + name + ")", HPI.SQLError.SYNTAX_ERR);
		}
		
		var n						= name.toUpperCase()
			,tblSch				= table.schema

	    var condition = args.deleteCondition instanceof SQLParser.nodes.Where ?
										args.deleteCondition.conditions : true
			, ra = 0
			, remainingRows = []
			, _row = {}
			, allFields = tblSch.columns
		    , pkField = tblSch.pkField || ''
			, _keys = tblSch.keys || {};

		remainingRows = table.data; //this.readFromStorage(name);

		//all are to be deleted
		if( condition === true ){
			ra					   = remainingRows.length;
			remainingRows = [];
		}
		else{
		
			var _remainingRows = remainingRows.concat([]);
		
			for(var i = 0, len = _remainingRows.length; i < len; i++){
				_row = _remainingRows[i];
				if( this._evalExpInRel(condition, _row, allFields) === true ){
					if( pkField ){
						if( _keys[_row[pkField]] !== undefined ){
							delete _keys[_row[pkField]];
						}
					}
					
					ra++;
					delete _remainingRows[i];
				}
			}
			
			if( ra > 0 ){
				remainingRows = [];
				for(var i = 0, len = _remainingRows.length; i < len; i++){
					var t = _remainingRows[i];
					if( t === undefined ) continue;
					remainingRows = remainingRows.concat([_remainingRows[i]]);
				}
			}
			
				
		}

		table = HPI.apply(table, {
			data : remainingRows
		   ,schema : HPI.apply(table.schema, {
				keys : _keys
			})
		});
		
		this._persistDBObj('table',  table, {rowsAffected :ra, insertId : undefined}, callbacks, sqlTrxn);
		
	}
	
	,_createTable : function(name, args, callbacks, sqlTrxn){
	
		if( this._isDBObjDefined(name, 'table') ){
			if( args.ifNotExists ) {
				if( typeof callbacks.cbSuccess == 'function' ){
					callbacks.cbSuccess.apply(window, [sqlTrxn, new HPI.SQLResultSet({rowsAffected : 0, insertId : undefined})]);
				}
				return;
			}
			//note: testing in chrome yields SYNTAX_ERR for attempting to create an existing table
			throw new HPI.SQLError("could not prepare statement (1 table " + name + " already exists)", HPI.SQLError.SYNTAX_ERR);
		}
		
		var data =  []
		   ,schema = {
				columns : args.fields
			   ,fIndex : args.fIndex
			   ,fIndexOrigNames : args.fIndexOrigNames
			   ,pkField : args.pkField
			   ,autoIncField : args.autoIncField
			   ,keys     : args.keys
			   ,nonNullFields : args.nonNullFields
			   ,autoIncField : args.autoIncField
			   ,lastInsertId : 0				
			};
		
		this._persistDBObj('table', {schema : schema, data : data, name : name}, {rowsAffected : 0, insertId : undefined}, callbacks, sqlTrxn);	
		
	}
	
	,_dropTable : function(name, args, callbacks, sqlTrxn){

		if(  !this._isDBObjDefined(name, 'table') ){
			if( args.ifExists == false ){
				//note: testing in chrome yields SYNTAX_ERR for attempting to drop a non-existent table
				throw new HPI.SQLError("could not prepare statement (1 no such table: " + name + ")", HPI.SQLError.SYNTAX_ERR);
			}
			
			if( typeof callbacks.cbSuccess == 'function' ){
				callbacks.cbSuccess.apply(window, [sqlTrxn, new HPI.SQLResultSet({rowsAffected : 0, insertId : undefined})]);
			}

			return;
		}

		this._deleteDBObj('table', {name : name}, {rowsAffected : 0, insertId : undefined}, callbacks, sqlTrxn);

	}

    //last rows affected (deprecated)
	,_lra : 0
	//(deprecated)
    ,_lastRowsAffected : function(){
		return this._lra;
   }

    //last insert id (deprecated)  
    ,_lid : 0
	//(deprecated)
    ,_lastInsertId : function(){
		return this._lid;
   }
   
	,tblInfo : function(table, prop){
		
		var obj = this._getDBObj(table, "table");
		if( !obj){
            throw new HPI.SQLError("could not prepare statement (1 no such table: " + table + ")", HPI.SQLError.SYNTAX_ERR);
        }
		
		return obj.schema[prop];

	}
	
	//abstract
	,_isDBObjDefined : function(name, objecType){}
	
	//abstract
	,_getDBObj : function(name, objectType){}
	
	//abstract
	,_persistDBObj : function(objectType, def, result, callbacks, sqlTrxn, isFromInit){}
	
	//abstract
	,readFromStorage : function(tableName){}
	
	//abstract
	,_deleteDBObj : function(objectType, def, result, callbacks, sqlTrxn){}

 });


//implementation for LocalStorage
HPI.LocalStorage = HPI.AbstractWebStorage.extend({

	init : function(cfg){
		
		HPI.apply(this, cfg);

	}
	
    ,_locStor : null
	
   	,_storage : function(){
   
		if( !this._locStor ) {
			this._locStor = window.localStorage;
		}
		
		return this._locStor;
		
    }

	,_prepare  : function(onReady, scope){
	
		var mode = this.mode ? this.mode.toLowerCase() : 'async';
		if( mode == 'sync' ){
			onReady.apply(scope, []);
			return;
		}
		
		setTimeout(function(){
			onReady.apply(scope, []);
		}, 10);
		
	}
	
	,_persistDBObj : function(objectType, def, result, callbacks, sqlTrxn, isFromInit){
	
        isFromInit = isFromInit || false;

        var exception = null, size = 0;
		if( objectType.toUpperCase() == 'TABLE' ){

			try{

                var stor = this._storage()
					,dbName = this.dbName
					,_def = {
						"schema" : {"tables" : {}}
					   ,"data" : {"tables" : {}}
					};
					
				if( !isFromInit ){
					var tableName = def.name;
					_def["schema"]["tables"][tableName] = def.schema;
					_def["data"]["tables"][tableName] = {data : def.data};
				}
				else{
					_def = def;
				}
				
				for(var key in _def.schema.tables){
					
					var completeName = (dbName + "." + objectType + "." + key).toUpperCase();
					
					stor.removeItem(completeName);
					var data = JSON.stringify({schema : _def.schema.tables[key], data : _def.data.tables[key].data, name :  key.toUpperCase()});
					size = size + data.length;
					stor.setItem(completeName, data);

				}

			}catch(e){
                exception = e;
			}

		}
        
        size = Math.round((size/1024/1024) * 100) / 100;
        var info = {dataSize : size};

        if( !exception ){
            if( typeof callbacks.cbSuccess == 'function' ) {
					callbacks.cbSuccess.apply(window, [sqlTrxn, new HPI.SQLResultSet(result), info]);
		    }
            return;
        }

        if( typeof callbacks.cbError == 'function' ) {
            
            var msg =exception.message.toString().trim(), code = exception.code;
            
            // aside from the standard DOM error, occasionally this message appears on IE9 when data is too large.
            info.isSpecialError = (msg == "Not enough storage is available to complete this operation.");
            
            if( 
                exception.code == 22 ||
                info.isSpecialError
            ){
                
                code = HPI.SQLError.TOO_LARGE_ERR;
                
                msg =  "Current data size exceeds the limit for the current database version";
            }

            callbacks.cbError.apply(window, [sqlTrxn, new HPI.SQLError(msg, code), info]);

		}

	}
	
	,_deleteDBObj : function(objectType, def, result, callbacks, sqlTrxn){
	
		try{
			this._storage().removeItem((this.dbName + "." + objectType + "." + def.name).toUpperCase());
			if( typeof callbacks.cbSuccess == 'function' ) {
				callbacks.cbSuccess.apply(window, [sqlTrxn, new HPI.SQLResultSet(result)]);
			}
		}
		catch(e){
			 if( typeof callbacks.cbError == 'function' ) {
				 callbacks.cbError.apply(window, [sqlTrxn, new HPI.SQLError(e.message.toString().trim(), e.code), info]);
			 }
		}
		
	}
	
	,_mInit : function(def, success, error){
   
        var obj = this;
        var fn = function(){
            var ra = def.rowsAffected || 0;
            delete def.rowsAffected;
            
            obj._persistDBObj('table', def, {rowsAffected : ra}, {cbSuccess : success, cbError : error }, new HPI.SQLTransaction(null), true);
        };

        this._prepare(fn, this);
   }

	//read user data
	,readFromStorage : function(tableName){

		var obj = this._getDBObj(tableName, 'table');
		
		if( !obj ){
			throw new HPI.SQLError("could not prepare statement (1 no such table: " + tableName + ")", HPI.SQLError.SYNTAX_ERR);
		}
		
		return obj.data;

	}
	
	,_isDBObjDefined : function(name, objectType){
	
		return this._getDBObj(name, objectType) != null;

	}
	
	,_getDBObj : function(name, objectType){
		

		var stor = this._storage()
			,obj  = stor.getItem((this.dbName + "." + objectType + "." + name).toUpperCase());
		
		return JSON.parse(obj || null);		
		
	}	
	
});

//implementation for IndexedDB
HPI.IndexedDB = HPI.AbstractWebStorage.extend({
	
	_idxDB: null
 
	, init: function (config) {
	    
		HPI.apply(this, config);
		this._idxDB = $.indexedDB;
		this._createDB(this.dbName);
		
	}

	,requestTransaction : function(sqlTrxnCallback){

		var	me	 = this
		       ,attempts = 0
			   ,intrvl = function(){
					attempts++;
					if( me._isReady ){
						clearInterval(id);
						sqlTrxnCallback();
					}
					
					if( attempts == 100 ){
						throw new HPI.SQLError("Cannot grant transaction, db not ready.");
					}
					
				}
		
		var id = setInterval(intrvl, 0);
		
	}
	
    ,_prepared : false
	,_prepare  : function(onReady, scope){

		var startDate = new Date();
        var log = function(){
            var duration = ((new Date() - startDate)/1000).toString() + " seconds.";
	        console.log(duration);
        }

        var count = 0
            ,me = this;

        var _onReady = function(){
            log();
            me._prepared = true;
            onReady.apply(scope, []);
        }

        if( me._prepared ){
            _onReady();
            return;
        }
		
		
        me._dbObjs = me._dbObjs ? me._dbObjs : {};

		this._idxDB(this.dbName).objectStore(this.dbName).count().done(function(numOfItems){			

            if( numOfItems == 0 ){
               _onReady();
               return;
            }

			me._idxDB(me.dbName).objectStore(me.dbName).each(function(item){               

				count++;
				me._dbObjs[item.value.component] = item.value.data;

                if( count == numOfItems ){
                    _onReady();
                    return;
                }

			});
			
		});
		
	}	
	
	,_dbObjs : null
	,_dbObjects : function(){
		
		return this._dbObjs;
	}
	
    ,_isReady : true
    , _createDB: function (dbName) {
		
        var	me 					= this
			   ,idxDB				= this._idxDB;


		idxDB(dbName, {
			"version" : 1
		   ,"schema" : {
		   
				1 : function (trans) {
						
						me._isReady = false;
						var obj = trans.createObjectStore(dbName, {
							keyPath: "component", autoIncrement: false
						});
						if( !obj ){
							throw new HPI.SQLError("Cannot create db. Please refresh page.");
						}
						obj.createIndex("component", { "unique": true, "multiEntry": true });
						
				   }
			}
		}).done(function(){
			
			if( me._isReady == false ) {
				idxDB(dbName).transaction(dbName).then(
					function(){
						me._isReady = true;
						console.log('db creation completed.');
					}
				   ,function(){
						console.log('db creation failed.');
						throw new HPI.SQLError("failed to create database");
				   }
				   ,function(transaction){						
				   }
				);
			}
			
		});

    }

	//persist/drop a data object (e.g., table)
	, _persistDBObj : function(objectType, def, result, callbacks, sqlTrxn, isFromInit){
	
		var dbName 			= this.dbName
			,me 					= this;
		
        var info = {};
			 
		var handleSuccess = function(){
			if( typeof callbacks.cbSuccess == 'function' ){
				callbacks.cbSuccess.apply(window, [sqlTrxn, new HPI.SQLResultSet(result), info]);
			}
		};
		
		var handleError = function(err, e){
			if( typeof callbacks.cbError == 'function' ){
				callbacks.cbError.apply(window, [sqlTrxn, new HPI.SQLError('IndexedDB Error'), info]);
			}
		}
		
		var handlSetData = function(transaction){

			
			var dbName = me.dbName
				,_def = {
						"schema" : {"tables" : {}}
					   ,"data" : {"tables" : {}}
				};
					
			if( !isFromInit ){
				var tableName = def.name;
				_def["schema"]["tables"][tableName] = def.schema;
				_def["data"]["tables"][tableName] = {data : def.data};
			}
			else{
				_def = def;
			}
			
			var db = transaction.objectStore(dbName), size = 0;

			for(var key in _def.schema.tables){
					
				var completeName = (dbName + "." + objectType + "." + key).toUpperCase();
				var dbObj = {
						schema : _def.schema.tables[key]
						,data : _def.data.tables[key].data
						,name :  key.toUpperCase()
					}
				   ,_data = JSON.stringify(dbObj);
				   
				size = size + _data.length;
				transaction.objectStore(dbName).delete(completeName);
				db.add({"component" : completeName, "data" : dbObj});
				me._dbObjs[completeName] = dbObj;
				
			}

			info = {dataSize : Math.round((size/1024/1024) * 100) / 100};

		}
		
		if( objectType.toUpperCase() == 'TABLE' ){
		
			this._idxDB(dbName).transaction(dbName).then(
				handleSuccess
			   ,handleError
			   ,handlSetData
			);
			
		}
		
	}
	
	,_deleteDBObj : function(objectType, def, result, callbacks, sqlTrxn){
	
		var dbName 			= this.dbName
			,me = this;
			
		if( objectType.toUpperCase() == 'TABLE' ){
		
			this._idxDB(dbName).transaction(dbName).then(
				//success
				function(){
					if( typeof callbacks.cbSuccess == 'function' ){
						callbacks.cbSuccess.apply(window, [sqlTrxn, new HPI.SQLResultSet(result), {}]);
					}
				}
				
				//error
			   ,function(err, e){
					if( typeof callbacks.cbError == 'function' ){
						callbacks.cbError.apply(window, [sqlTrxn, new HPI.SQLError('IndexedDB Error'), {}]);
					}
				}
				//set data
			   ,function(transaction){
					var completeName = (dbName + "." + objectType + "." + def.name).toUpperCase();
					transaction.objectStore(dbName).delete(completeName);
					delete me._dbObjs[completeName];
				}
			   
			);
			
		}
		
	}
	
	,_mInit : function(def, success, error){
       
        var obj = this;
        var fn = function(){
            var ra = def.rowsAffected || 0;
            delete def.rowsAffected;
            obj._persistDBObj('table', def, {rowsAffected : ra}, {cbSuccess : success, cbError : error }, new HPI.SQLTransaction(null), true);
        };

        this._prepare(fn, this);
    }	

	,readFromStorage : function(tableName){

		var obj = this._getDBObj(tableName, 'table');
		
		if( !obj ){
			throw new HPI.SQLError("could not prepare statement (1 no such table: " + tableName + ")", HPI.SQLError.SYNTAX_ERR);
		}
		
		return obj.data;

	}
	
	,_isDBObjDefined : function(name, objectType){
	
		return this._getDBObj(name, objectType) != null;

	}
	
	,_getDBObj : function(name, objectType){
		

		var _do = this._dbObjects();
		
		return _do[(this.dbName + "." + objectType + "." + name).toUpperCase()] || null;
		
	}
	
 });